IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetFormTypes]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetFormTypes]
GO
/******************************************************************
* Name:			[GetFormTypes]
* Purpose:		Get Forms type available for the Protocol Process
*
* PARAMETERS
* Name    					Description					
* -------------  			-------------------------------------------
* @ProtocolProcessid		Id of the Process. 0 will do an insert.

******************************************************************/
CREATE Procedure [dbo].[GetFormTypes] (  
  
  @protocolProcessId  INTEGER   
)  
AS  
BEGIN  
  
 SET NOCOUNT ON  

	DECLARE @ProtocolVersionId INT
 
	SELECT @ProtocolVersionId =  pv.[Id]
	FROM ProtocolVersion pv 
	INNER JOIN ProtocolProcess pp 
	ON pv.[ProtocolProcessId]=pp.[Id]   
	WHERE pp.[Id]=@protocolProcessId 
   	AND pv.version=(SELECT MAX(version)
   					FROM protocolversion 
					WHERE protocolProcessId=@protocolProcessId)   

	SELECT 	QA.Id,    
	   		Q.Name
 	FROM dbo.QuestionnaireAnswer QA    
  	INNER JOIN dbo.QuestionnaireVersion QV     
   		on QA.QuestionnaireVersionId = QV.ID    
  	INNER JOIN dbo.Questionnaire Q    
  	 	on QV.QuestionnaireId = Q.Id    
  	INNER JOIN dbo.ProtocolVersion_QuestionnaireAnswer_Map map    
  	 	on (map.QuestionnaireAnswerId = QA.Id and map.ProtocolVersionId = @protocolVersionId)    
  	LEFT JOIN dbo.RefCodeAttribute RCA 
		ON RCA.RefCode = Q.Category AND RCA.Attribute = 'ORDER'  
	  LEFT JOIN dbo.AdmPerson ADP  
	   	ON ADP.Id = QA.LastModifiedByAdmPersonId  
	ORDER BY RCA.Value, Q.CategorySequenceNo  
     
END
 
GO

